import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_学历性别交叉分组下各毕业去向及落实率分布情况(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    学历性别交叉分组下各毕业去向及落实率分布情况 - 指标计算函数
    
    ## 指标说明
    该指标用于统计不同学历、性别分组下的毕业去向分布情况及落实率。
    包含三个子指标：
    1. 各学历下不同性别在不同毕业去向的人数占比：按学历+性别+毕业去向分组，计算各毕业去向人数占该学历性别组总人数的比例
    2. 各性别在不同毕业去向的人数占比：按性别+毕业去向分组，计算各毕业去向人数占该性别组总人数的比例
    3. 毕业去向落实率：落实率 = 落实人数 / 总人数，其中落实项为毕业去向不为'待就业'和'暂不就业'的所有学生
       - 各学历各性别的落实率
       - 各性别的落实率
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": {
            "education_sex_distribution": [
                {
                    "education": "本科毕业生",
                    "sex": "男",
                    "grad_dest_merge": "单位就业",
                    "count": 500,
                    "ratio": 0.625,
                    "total": 800
                },
                {
                    "education": "本科毕业生",
                    "sex": "男",
                    "grad_dest_merge": "升学",
                    "count": 200,
                    "ratio": 0.25,
                    "total": 800
                }
            ],
            "sex_distribution": [
                {
                    "sex": "男",
                    "grad_dest_merge": "单位就业",
                    "count": 1500,
                    "ratio": 0.6,
                    "total": 2500
                },
                {
                    "sex": "男",
                    "grad_dest_merge": "升学",
                    "count": 600,
                    "ratio": 0.24,
                    "total": 2500
                }
            ],
            "settlement_rate": {
                "by_education_sex": [
                    {
                        "education": "本科毕业生",
                        "sex": "男",
                        "settlement_count": 700,
                        "total": 800,
                        "settlement_rate": 0.875
                    },
                    {
                        "education": "本科毕业生",
                        "sex": "女",
                        "settlement_count": 650,
                        "total": 700,
                        "settlement_rate": 0.9286
                    }
                ],
                "by_sex": [
                    {
                        "sex": "男",
                        "settlement_count": 2200,
                        "total": 2500,
                        "settlement_rate": 0.88
                    },
                    {
                        "sex": "女",
                        "settlement_count": 1800,
                        "total": 2000,
                        "settlement_rate": 0.9
                    }
                ]
            }
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 学历性别交叉分组下各毕业去向及落实率分布情况, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建学生表名
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"

        # 4. 查询学生客观表数据
        # 4.1 子指标1：各学历各性别下不同毕业去向分布
        sql_1 = f"""
        SELECT
            education,
            sex,
            grad_dest_merge,
            COUNT(*) as count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
        GROUP BY education, sex, grad_dest_merge
        ORDER BY education, sex, grad_dest_merge
        """
        education_sex_dest_results = db.fetchall(sql_1, (shard_tb_key,))
        
        # 4.2 子指标2：各性别下不同毕业去向分布
        sql_2 = f"""
        SELECT
            sex,
            grad_dest_merge,
            COUNT(*) as count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
        GROUP BY sex, grad_dest_merge
        ORDER BY sex, grad_dest_merge
        """
        sex_dest_results = db.fetchall(sql_2, (shard_tb_key,))
        
        # 4.3 查询各学历各性别的总人数（用于计算占比和落实率）
        sql_3 = f"""
        SELECT
            education,
            sex,
            COUNT(*) as total_count,
            SUM(CASE WHEN grad_dest_merge NOT IN ('待就业', '暂不就业') THEN 1 ELSE 0 END) as settlement_count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
        GROUP BY education, sex
        """
        education_sex_totals = db.fetchall(sql_3, (shard_tb_key,))
        
        # 4.4 查询各性别的总人数（用于计算占比和落实率）
        sql_4 = f"""
        SELECT
            sex,
            COUNT(*) as total_count,
            SUM(CASE WHEN grad_dest_merge NOT IN ('待就业', '暂不就业') THEN 1 ELSE 0 END) as settlement_count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
        GROUP BY sex
        """
        sex_totals = db.fetchall(sql_4, (shard_tb_key,))

        # 5. 处理数据并计算结果
        # 5.1 构建各学历各性别的总人数映射（用于计算占比）
        education_sex_total_map = {}
        education_sex_settlement_map = {}
        for row in education_sex_totals:
            key = (row['education'], row['sex'])
            education_sex_total_map[key] = row['total_count']
            education_sex_settlement_map[key] = row['settlement_count']
        
        # 5.2 构建各性别的总人数映射（用于计算占比）
        sex_total_map = {}
        sex_settlement_map = {}
        for row in sex_totals:
            sex_total_map[row['sex']] = row['total_count']
            sex_settlement_map[row['sex']] = row['settlement_count']
        
        # 5.3 子指标1：各学历各性别下不同毕业去向占比
        education_sex_result = []
        for row in education_sex_dest_results:
            education = row['education']
            sex = row['sex']
            key = (education, sex)
            total = education_sex_total_map.get(key, 0)
            count = row['count']
            ratio = round(count / total, 4) if total > 0 else 0
            
            education_sex_result.append({
                'education': education,
                'sex': sex,
                'grad_dest_merge': row['grad_dest_merge'],
                'count': count,
                'ratio': ratio,
                'total': total
            })
        
        # 5.4 子指标2：各性别下不同毕业去向占比
        sex_result = []
        for row in sex_dest_results:
            sex = row['sex']
            total = sex_total_map.get(sex, 0)
            count = row['count']
            ratio = round(count / total, 4) if total > 0 else 0
            
            sex_result.append({
                'sex': sex,
                'grad_dest_merge': row['grad_dest_merge'],
                'count': count,
                'ratio': ratio,
                'total': total
            })
        
        # 5.5 毕业去向落实率计算（正确的计算方式）
        # 落实率 = 落实人数 / 总人数
        # 落实项 = ['单位就业', '自主创业', '自由职业', '升学']
        
        # 5.5.1 各学历各性别的落实率
        education_sex_settlement = []
        for row in education_sex_totals:
            education = row['education']
            sex = row['sex']
            total = row['total_count']
            settlement_count = row['settlement_count']
            settlement_rate = round(settlement_count / total, 4) if total > 0 else 0
            
            education_sex_settlement.append({
                'education': education,
                'sex': sex,
                'settlement_count': settlement_count,
                'total': total,
                'settlement_rate': settlement_rate
            })
        
        # 5.5.2 各性别的落实率
        sex_settlement = []
        for row in sex_totals:
            sex = row['sex']
            total = row['total_count']
            settlement_count = row['settlement_count']
            settlement_rate = round(settlement_count / total, 4) if total > 0 else 0
            
            sex_settlement.append({
                'sex': sex,
                'settlement_count': settlement_count,
                'total': total,
                'settlement_rate': settlement_rate
            })

        logger.info(f"指标 '学历性别交叉分组下各毕业去向及落实率分布情况' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": {
                "education_sex_distribution": education_sex_result,
                "sex_distribution": sex_result,
                "settlement_rate": {
                    "by_education_sex": education_sex_settlement,
                    "by_sex": sex_settlement
                }
            }
        }

    except Exception as e:
        logger.error(f"计算指标 '学历性别交叉分组下各毕业去向及落实率分布情况' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 学历性别交叉分组下各毕业去向及落实率分布情况",
            "code": 500,
            "error": str(e)
        }